using System;
using System.IO;
using System.Threading.Tasks;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using MySql.Data.MySqlClient;

namespace MT5Trade.Tests.TestFramework
{
    /// <summary>
    /// 测试数据初始化器
    /// 负责执行SQL脚本和初始化测试数据库
    /// </summary>
    public class ArbitrageTestDataInitializer
    {
        private readonly string _connectionString;
        private readonly ILogger<ArbitrageTestDataInitializer> _logger;
        private readonly string _scriptsPath;

        public ArbitrageTestDataInitializer(IConfiguration configuration, ILogger<ArbitrageTestDataInitializer> logger)
        {
            _connectionString = configuration.GetConnectionString("TestDatabase")
                ?? "Server=stage-db-server;Port=13306;Database=xm5;Uid=testuser;Pwd=testpass;";
            _logger = logger;
            _scriptsPath = Path.Combine(Directory.GetCurrentDirectory(), "Scripts");
        }

        /// <summary>
        /// 执行SQL脚本文件
        /// </summary>
        public async Task<bool> ExecuteSqlScriptAsync(string scriptFileName)
        {
            try
            {
                var scriptPath = Path.Combine(_scriptsPath, scriptFileName);

                if (!File.Exists(scriptPath))
                {
                    _logger.LogError($"SQL script not found: {scriptPath}");
                    return false;
                }

                var script = await File.ReadAllTextAsync(scriptPath);

                using var connection = new MySqlConnection(_connectionString);
                await connection.OpenAsync();

                // MySQL不支持GO语句，需要按分号分割
                var commands = script.Split(new[] { ';' }, StringSplitOptions.RemoveEmptyEntries);

                foreach (var commandText in commands)
                {
                    var trimmedCommand = commandText.Trim();
                    if (string.IsNullOrWhiteSpace(trimmedCommand))
                        continue;

                    try
                    {
                        using var command = new MySqlCommand(trimmedCommand, connection);
                        await command.ExecuteNonQueryAsync();
                    }
                    catch (MySqlException ex)
                    {
                        // 如果是存储过程或函数已存在的错误，可以忽略
                        if (ex.Number == 1304) // PROCEDURE already exists
                        {
                            _logger.LogWarning($"Procedure/Function already exists: {ex.Message}");
                            continue;
                        }
                        throw;
                    }
                }

                _logger.LogInformation($"Successfully executed SQL script: {scriptFileName}");
                return true;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"Failed to execute SQL script: {scriptFileName}");
                return false;
            }
        }

        /// <summary>
        /// 初始化所有测试表和数据
        /// </summary>
        public async Task<bool> InitializeAllTestDataAsync()
        {
            try
            {
                _logger.LogInformation("Initializing all test data...");

                // 1. 创建表结构
                if (!await ExecuteSqlScriptAsync("CreateTestTables.sql"))
                {
                    _logger.LogError("Failed to create test tables");
                    return false;
                }

                // 2. 插入市场条件测试数据
                if (File.Exists(Path.Combine(_scriptsPath, "InsertMarketConditionData.sql")))
                {
                    await ExecuteSqlScriptAsync("InsertMarketConditionData.sql");
                }

                // 3. 插入交易历史数据
                if (File.Exists(Path.Combine(_scriptsPath, "InsertTradeHistory.sql")))
                {
                    await ExecuteSqlScriptAsync("InsertTradeHistory.sql");
                }

                // 4. 插入边界测试数据
                if (File.Exists(Path.Combine(_scriptsPath, "InsertBoundaryTestData.sql")))
                {
                    await ExecuteSqlScriptAsync("InsertBoundaryTestData.sql");
                }

                _logger.LogInformation("Test data initialization completed successfully");
                return true;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Failed to initialize test data");
                return false;
            }
        }

        /// <summary>
        /// 验证测试数据完整性
        /// </summary>
        public async Task<bool> VerifyTestDataIntegrityAsync()
        {
            try
            {
                using var connection = new MySqlConnection(_connectionString);
                await connection.OpenAsync();

                // 检查各个表的数据量
                var tables = new[]
                {
                    "arbitrage_test_scenarios",
                    "garch_test_data",
                    "var_test_data",
                    "market_condition_test_data"
                };

                foreach (var table in tables)
                {
                    using var command = new MySqlCommand($"SELECT COUNT(*) FROM {table}", connection);
                    var count = Convert.ToInt32(await command.ExecuteScalarAsync());

                    if (count == 0)
                    {
                        _logger.LogWarning($"Table {table} is empty");
                        return false;
                    }

                    _logger.LogInformation($"Table {table} has {count} records");
                }

                return true;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Failed to verify test data integrity");
                return false;
            }
        }
    }
}